package com.example.glgk.utils;

import com.example.glgk.dto.QueryEscalation;
import com.example.glgk.excel.ExcelDto;
import com.example.glgk.excel.ExcelField;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static org.apache.commons.lang3.StringUtils.isNumeric;

/**
 * @author lxk
 * Excel导出工具类
 */
public class ExcelUtils {

    //标题头的行数，根据需要的格式调整
    static private final int HEAD_ROW_COUNT = 3;

    /**
     * 获取一个基本的带边框的单元格
     * @param workbook
     * @return
     */
    private static HSSFCellStyle getBasicCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle hssfcellstyle = workbook.createCellStyle();
        hssfcellstyle.setBorderLeft(BorderStyle.THIN);
        hssfcellstyle.setBorderBottom(BorderStyle.THIN);
        hssfcellstyle.setBorderRight(BorderStyle.THIN);
        hssfcellstyle.setBorderTop(BorderStyle.THIN);
        hssfcellstyle.setAlignment(HorizontalAlignment.CENTER);
        hssfcellstyle.setVerticalAlignment(VerticalAlignment.CENTER);
        hssfcellstyle.setWrapText(true);
        return hssfcellstyle;
    }

    public static <T> void exportExcel(HttpServletResponse response, List<T> dataList, Class clazz,QueryEscalation queryEscalation) {
        ExcelDto export = (ExcelDto) clazz.getAnnotation(ExcelDto.class);
        String fileName;
        if (export == null || StringUtils.isEmpty(export.fileName())) {
            fileName = "exportExcel.xls";
        } else {
            fileName = export.fileName() + ".xls";
        }
        OutputStream os = null;
        try {
            os = getBufferedOutputStream(fileName, response);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        exportExcel(os, dataList, clazz,queryEscalation);
    }

    private static BufferedOutputStream getBufferedOutputStream(String fileName, HttpServletResponse response) throws Exception {
        response.setContentType("application/x-msdownload");
        response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        return new BufferedOutputStream(response.getOutputStream());
    }

    /**
     * 导出 Excel
     * @param outputStream 输出流，用于写文件
     * @param dataList     需要导出的数据
     * @param clazz        导出数据的pojo类型
     */
    public static <T> void exportExcel(OutputStream outputStream, List<T> dataList, Class clazz,QueryEscalation queryEscalation) {
        // 定义一个集合来存储实际的Map的key
        List<String> dynamicKeys = new ArrayList<>();
        //创建一个Excel工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //建立表
        HSSFSheet hssfsheet = workbook.createSheet();

        hssfsheet.setDefaultRowHeight((short) (20 * 20));
        //检查当前类是否允许导出
        //ExcelDto export = (ExcelDto) clazz.getAnnotation(ExcelDto.class);
        Map<Integer, String> colNames = new HashMap<>();
        //所有允许导出的字段
        Map<Integer, Field> fieldMap = new HashMap();
        for (Field field : clazz.getDeclaredFields()) {
            if (field.isAnnotationPresent(ExcelField.class)) {
                ExcelField excelField = field.getAnnotation(ExcelField.class);
                colNames.put(excelField.index(), excelField.colName());
                if (fieldMap.get(excelField.index()) != null) {
                    throw new RuntimeException("注解中设置的index有重复，请检查注解代码。");
                }
                fieldMap.put(excelField.index(), field);
                //设置每列的宽度
                hssfsheet.setColumnWidth(excelField.index(), excelField.colWidth() * 20);
            } else if (field.getType() == Map.class) {
                // 处理 Map 类型的动态列
                ParameterizedType parameterizedType = (ParameterizedType) field.getGenericType();
                Class<?> mapKeyType = (Class<?>) parameterizedType.getActualTypeArguments()[0];
                if (mapKeyType == String.class) {
                    // 这里就要将数据集合里面存储的实际的key作为需要被设置导出的表头，而不是把这个map作为表头
                    field.setAccessible(true);
                    Map<String, ?> dynamicColumnsValue = null;
                    try {
                        dynamicColumnsValue = (Map<String, ?>) field.get(dataList.get(0));
                    } catch (Exception e) {
                        throw new RuntimeException(e);
                    }
                    // 将实际的 Map 的 key 存储到 dynamicKeys 中
                    dynamicKeys.addAll(dynamicColumnsValue.keySet());
                    fieldMap.put(fieldMap.size(), field);
                    hssfsheet.setColumnWidth(fieldMap.size(), 2300);
                }
            }
        }
        //校验是否有不连续的index
        for (int i = 0; i < fieldMap.size(); i++) {
            if (fieldMap.get(i) == null) {
                throw new RuntimeException("注解中设置的index不连续（必须为从0开始的连续整数），请检查注解代码。");
            }
        }

        HSSFRow hssfRow = null;

        //绘制标题
        //createTitle(workbook, hssfsheet, colNames.size() - 1, export.fileName());
        //创建标题行（表头）
        int size = colNames.size();
        for (int i = 0; i < dynamicKeys.size(); i++) {
            colNames.put(size + i, dynamicKeys.get(i));
        }
        createHeadRow(workbook, hssfsheet, colNames);
        //表格样式
        HSSFCellStyle cellStyle = getBasicCellStyle(workbook);
        // 创建大表头行
        HSSFRow headerRow = hssfsheet.createRow(0);
        headerRow.setHeightInPoints((short) 30);
        HSSFCell headerCell = headerRow.createCell(0);
        // 创建字体样式
        HSSFFont font = workbook.createFont();
        font.setBold(true); // 设置为加粗
        font.setFontHeightInPoints((short) 22); // 设置字体大小
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        headerCell.setCellStyle(style);
        setTitle(queryEscalation.getTimes(),headerCell);
        // 合并大表头单元格
        hssfsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colNames.size() - 1));

        // 创建小字行
        HSSFRow subtitleRow = hssfsheet.createRow(1);
        HSSFCell subtitleCell = subtitleRow.createCell(0);
        subtitleCell.setCellValue("编制单位：南京鼓楼医院");
        // 合并小字单元格
        hssfsheet.addMergedRegion(new CellRangeAddress(1, 1, 0, colNames.size() - 1));
        //插入内容
        int i = 0;
        //逐行设置值
        if (!CollectionUtils.isEmpty(dataList)) {
            for (Object obj : dataList) {
                hssfRow = hssfsheet.createRow(i + HEAD_ROW_COUNT);
                for (int j = 0; j < fieldMap.size(); j++) {
                    HSSFCellStyle basicCellStyle = getBasicCellStyle(workbook);
                    if(j == 0){
                        //项目列需要靠左
                        basicCellStyle.setAlignment(HorizontalAlignment.LEFT);
                    }
                    Field field = fieldMap.get(j);
                    field.setAccessible(true);
                    Object value = null;
                    try {
                        value = field.get(obj);
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException(e);
                    }
                    //如果为Map的话 就把当前Map的key设置表头，value设置成value
                    if (field.getType() == Map.class) {
                        HashMap<String, String> map = (HashMap<String, String>) value;
                        for (String dynamicKey : dynamicKeys) {
                            String result = map.get(dynamicKey);
                            int columnIndex = j + dynamicKeys.indexOf(dynamicKey);
                            hssfsheet.autoSizeColumn(columnIndex);
                            setCellValue(result, hssfRow, cellStyle, columnIndex);
                        }
                    } else {
                        hssfsheet.autoSizeColumn(j);
                        if(j==0){
                            setCellValue(value, hssfRow, basicCellStyle, j);
                        }else {
                            setCellValue(value, hssfRow, cellStyle, j);
                        }
                    }
                }
                i++;
            }
        }
        try {
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 设置表头标题栏以及表格高度
     *
     * @param workbook
     * @param hssfsheet
     * @param colNames
     */
    private static void createHeadRow(HSSFWorkbook workbook, HSSFSheet hssfsheet, Map<Integer, String> colNames) {
        //插入标题行
        HSSFRow hssfRow = hssfsheet.createRow(HEAD_ROW_COUNT - 1);
        for (int i = 0; i < colNames.size(); i++) {
            HSSFCell hssfcell = hssfRow.createCell(i);
            hssfcell.setCellStyle(getTitleCellStyle(workbook));
            hssfcell.setCellType(CellType.STRING);
            hssfcell.setCellValue(colNames.get(i));
        }
    }

    /**
     * 获取带有背景色的标题单元格
     *
     * @param workbook
     * @return
     */
    private static HSSFCellStyle getTitleCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle hssfcellstyle = getBasicCellStyle(workbook);
        hssfcellstyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); // 设置背景色
        hssfcellstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return hssfcellstyle;
    }

    /**
     * 设置单元格的值
     *
     * @param value
     * @param hssfRow
     * @param cellStyle
     * @param cellIndex
     */
    private static void setCellValue(Object value, HSSFRow hssfRow, CellStyle cellStyle, int cellIndex) {
        String valueStr = String.valueOf(value);
        HSSFCell hssfcell = hssfRow.createCell(cellIndex);
        if ("null".equalsIgnoreCase(valueStr)) {
            hssfcell.setCellStyle(cellStyle);
            return;
        }
        if (isNumeric(valueStr)) {
            if (!StringUtils.isEmpty(valueStr)) {
                hssfcell.setCellStyle(cellStyle);
                hssfcell.setCellType(CellType.NUMERIC);
                hssfcell.setCellValue(Double.valueOf(valueStr));
            }
        } else {
            hssfcell.setCellStyle(cellStyle);
            hssfcell.setCellType(CellType.STRING);
            hssfcell.setCellValue(valueStr);
        }
    }

    /**
     * 设置导出报告的时间处理
     * @author lxk
     *
     * @param times 时间
     * @param headerCell 单元格
     */
    public static void setTitleTime(List<String> times, HSSFCell headerCell){
        String year = "";
        //分为多年 和 当年
        for (int i = 0; i < times.size(); i++) {
            if (times.get(i).length() == 4) {
                String time = times.get(i);
                time = time + "年";
                times.set(i,time);
            }else {
                String time = times.get(i);
                year = time.substring(0,4) + "年";
                String month = Integer.parseInt(time.substring(5,7)) + "月";
                times.set(i,month);
            }
        }
        if(times.get(0).length() == 4){
            headerCell.setCellValue(String.join(",", times)+"三级公立医院绩效考核数据采集");
        }else {
            headerCell.setCellValue(year+String.join(",", times)+"三级公立医院绩效考核数据采集");
        }

    }

    /**
     * 设置导出报告的时间处理
     * @author lxk
     *
     * @param times 时间
     * @param headerCell 单元格
     */
    public static void setTitle(List<String> times, HSSFCell headerCell){
        String startTime = times.get(0);
        String endTime = times.get(times.size()-1);
        // 将原始日期字符串解析为 LocalDate 对象
        LocalDate startDate = LocalDate.parse(startTime);
        LocalDate endDate = LocalDate.parse(endTime);
        // 定义日期格式化器，将日期格式化为 yyyy年MM月dd日 格式
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy年MM月dd日");
        // 格式化日期
        String sDate = startDate.format(formatter);
        String eDate = endDate.format(formatter);

        headerCell.setCellValue(sDate+"-"+eDate + "三级公立医院绩效考核数据采集");
    }
}
